﻿using System;
using System.Collections.Generic;
using System.Text;
using ZhCun.Framework.Common.Models.TableModel;

namespace ZhCun.Framework.DataAccess.BuildSQLText
{
    public class BuildSQL_MSSQL: BuildSQL
    {
        public override void GenerateTopCountSQLText<T>(int topCount, string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, out string sqlText, params string[] selectFields)
        {
            StringBuilder strSql = new StringBuilder();
            if (selectFields == null || selectFields.Length == 0)
            {
                selectFields = GetAllFields<T>();
            }
            string searchFieldStr = GetSelectFieldStr(selectFields);
            string tableName = GetTableName<T>();
            strSql.AppendFormat("Select Top {0} {1} From {2} ", topCount, searchFieldStr, tableName);
            bool isWhere = false;
            if (CheckSqlWhere(ref sqlWhere))
            {
                isWhere = true;
            }
            else
            {
                dbParams = null;
                isWhere = false;
            }
            if (isWhere)
            {
                strSql.Append(sqlWhere);
            }
            string orderFieldStr;
            if (orderFields != null && orderFields.Length > 0)
            {
                orderFieldStr = GetSelectFieldStr(orderFields);
            }
            else
            {
                orderFieldStr = GetPrimaryKeyName<T>();
            }
            strSql.AppendFormat(" Order By {0} ", orderFieldStr);

            if (isDesc) strSql.Append("desc");
            sqlText = strSql.ToString();
        }

        public void GeneratePageSQLTextAndParam2<T>(int onePageCount, int pageNo, bool isDesc, string[] orderFields, int recordCount,
            string sqlWhere, Dictionary<string, object> dbParams, out string sqlText, params string[] selectFields) where T : ModelBase, new()
        {
            if (pageNo == 1)
            {
                //第一页使用top来查询
                GenerateTopCountSQLText<T>(onePageCount, sqlWhere, dbParams, orderFields, isDesc, out sqlText, selectFields);
                return;
            }

            StringBuilder sb = new StringBuilder();
            bool isWhere = false;
            if (CheckSqlWhere(ref sqlWhere))
            {
                isWhere = true;
            }
            else
            {
                dbParams = null;
                isWhere = false;
            }
            //recordCount = GetRecordCount(sqlWhere, dbParams);
            //pageCount = (int)(Math.Ceiling(recordCount * 1.0 / onePageCount));
            if (selectFields == null || selectFields.Length == 0)
            {
                selectFields = GetAllFields<T>();
            }
            string tableName = GetTableName<T>();
            string pkName = GetPrimaryKeyName<T>();
            string searchFieldStr = GetSelectFieldStr(selectFields);
            sb.AppendFormat("Select Top {0} {1} ", onePageCount, searchFieldStr);
            sb.AppendFormat("From {0} ", tableName);

            sb.AppendFormat("WHERE {0} NOT IN ( ", pkName);
            sb.AppendFormat("SELECT TOP ({0}*({1}-1)) {3} FROM {2}  ", onePageCount, pageNo, tableName, pkName);
            if (isWhere)
            {
                sb.Append(sqlWhere);
            }
            string orderFieldStr = GetSelectFieldStr(orderFields);
            if (string.IsNullOrEmpty(orderFieldStr))
            {
                orderFieldStr = pkName;
            }
            sb.AppendFormat("ORDER BY {0} ", orderFieldStr);
            if (isDesc)
            {
                sb.Append("Desc");
            }
            sb.Append(" ) ");
            if (isWhere)
            {
                //替换where为 and 
                sb.Append(sqlWhere.Replace("where", "and"));
            }
            sb.AppendFormat(" Order By {0} ", orderFieldStr);
            if (isDesc)
            {
                sb.Append("Desc");
            }
            sqlText = sb.ToString();
        }


        public override void GeneratePageSQLTextAndParam<T>(int onePageCount, int pageNo, bool isDesc, string[] orderFields, int recordCount,
            string sqlWhere, Dictionary<string, object> dbParams, out string sqlText, params string[] selectFields)
        {
            if (pageNo == 1)
            {
                //第一页使用top来查询
                GenerateTopCountSQLText<T>(onePageCount, sqlWhere, dbParams, orderFields, isDesc, out sqlText, selectFields);
                return;
            }
            StringBuilder sb = new StringBuilder();
            bool isWhere = false;
            if (CheckSqlWhere(ref sqlWhere))
            {
                isWhere = true;
            }
            else
            {
                dbParams = null;
                isWhere = false;
            }
            //recordCount = GetRecordCount(sqlWhere, dbParams);
            //pageCount = (int)(Math.Ceiling(recordCount * 1.0 / onePageCount));
            string tableName = GetTableName<T>();
            if (selectFields == null || selectFields.Length == 0)
            {
                selectFields = GetAllFields<T>();
            }
            //select * from (
            //    select top 100 ROW_NUMBER() OVER(ORDER BY yaopinbianma desc ) r ,* from VykYaoPinKuCunChaKan 
            //) T
            //where T.r> 20*(5-1)

            string searchFieldStr = GetSelectFieldStr(selectFields);
            string orderFieldStr = GetSelectFieldStr(orderFields);
            string orderStr = null;
            if (isDesc)
            {
                orderStr = " Desc ";
            }
            else
            {
                orderStr = "Asc";
            }
            sb.AppendFormat("Select top {1} {0} ", searchFieldStr,onePageCount);
            sb.AppendFormat("From (select top {0} ROW_NUMBER() OVER(ORDER BY {1} {2} ) r ,{4} from {3} ",
                onePageCount * pageNo, orderFieldStr, orderStr, tableName, searchFieldStr);
            if (isWhere)
            {
                sb.Append(sqlWhere);
                sb.AppendFormat("");
            }
            sb.AppendFormat(") T Where T.r> {0}*({1}-1)",onePageCount,pageNo);

            //sb.AppendFormat("WHERE {0} NOT IN ( ", PrimaryKeyName);
            //sb.AppendFormat("SELECT TOP ({0}*({1}-1)) {3} FROM {2}  ", onePageCount, pageNo, TableName, PrimaryKeyName);


            //if (string.IsNullOrEmpty(orderFieldStr))
            //{
            //    orderFieldStr = PrimaryKeyName;
            //}
            //sb.AppendFormat("ORDER BY {0} ", orderFieldStr);
            //if (isDesc)
            //{
            //    sb.Append("Desc");
            //}
            //sb.Append(" ) ");
            //if (isWhere)
            //{
            //    //替换where为 and 
            //    sb.Append(sqlWhere.Replace("where", "and"));
            //}
            //sb.AppendFormat(" Order By {0} ", orderFieldStr);
            //if (isDesc)
            //{
            //    sb.Append("Desc");
            //}
            sqlText = sb.ToString();
        }

    }
}
